﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Text;
using System.Text.RegularExpressions;
using NHibernate;
using VitCode.BaseDataPageCreator.Setting;
using VitCode.Castle.Helper;

namespace VitCode.BaseDataPageCreator
{
    /// <summary>
    /// 导入类
    /// </summary>
    public abstract class AbstractImportor
    {
        protected const string DELETE_CONTENT = "\\";
        protected const string INSERT = "导入", UPDATE = "更新";
        protected XmlEnumHelper m_enumHelper = new XmlEnumHelper();
        

        /// <summary>
        /// 
        /// </summary>
        protected ErrorResult m_ErrorMessage;

        /// <summary>
        /// 
        /// </summary>
        protected ExcelHelper m_ExecelHelper;

        private string m_FilePath;

        /// <summary>
        /// 
        /// </summary>
        protected DataTable m_SourceData;

        #region 基本属性

        private bool m_Checked;

        /// <summary>
        /// 历史描述
        /// </summary>
        protected string m_HistoryMemo;

        protected string m_ImportNum; //导入批次号yyyyMMddhhmmss
        private bool m_IsChangeDepartment = true;

        /// <summary>
        /// 是否对项目信息进行完整的验证
        /// </summary>
        protected bool m_IsTotalCheckProject = true;

        protected bool m_IsTrans;
        private string m_SheetName;

        /// <summary>
        /// 获取或设定文件路径名称
        /// </summary>
        public string FilePath
        {
            get
            {
                if (m_FilePath == null && m_SourceData == null)
                    throw new NullReferenceException("请输入Excel文件的路径");
                return m_FilePath;
            }
            set
            {
                if (value == null && m_SourceData == null)
                    throw new ArgumentNullException("FilePath");
                if (value.Length == 0)
                {
                    throw new ArgumentOutOfRangeException("FilePath", value.Length, "FilePath长度为0");
                }
                m_Checked = false;
                m_FilePath = value;
            }
        }

        public bool IsTrans
        {
            get { return m_IsTrans; }
            set { m_IsTrans = value; }
        }

        /// <summary>
        /// 历史描述
        /// </summary>
        public string HistoryMemo
        {
            get
            {
                if (m_HistoryMemo == null || m_HistoryMemo.Length == 0)
                    m_HistoryMemo = "设备导入";
                return m_HistoryMemo;
            }
            set { m_HistoryMemo = value; }
        }

        /// <summary>
        /// 获取或设定SheetName
        /// </summary>
        public string SheetName
        {
            get
            {
                if (m_SheetName == null && m_SourceData == null)
                    throw new NullReferenceException("请输入Excel中数据的Sheet名称");
                return m_SheetName;
            }
            set
            {
                if (value == null && m_SourceData == null)
                    throw new ArgumentNullException("SheetName");
                if (value.Length == 0)
                {
                    throw new ArgumentOutOfRangeException("SheetName", value.Length, "FilePath长度为0");
                }
                m_Checked = false;
                m_SheetName = value;
            }
        }

        /// <summary>
        /// 获取出错信息
        /// </summary>
        public virtual ErrorResult ErrorMessage
        {
            get
            {
                if (!Checked)
                {
                    throw new ArgumentException("在检查之前,获取检查信息");
                }
                return m_ErrorMessage;
            }
        }

        /// <summary>
        /// 是否已经检查过
        /// </summary>
        public bool Checked
        {
            get { return m_Checked; }
        }


        /// <summary>
        /// 获取更新的记录数
        /// </summary>
        public virtual int UpdateCount { get; set; }

        /// <summary>
        /// 获取导入的记录数
        /// </summary>
        public virtual int InsertCount { get; set; }

        /// <summary>
        /// 导入批次号
        /// </summary>
        public string ImportTime
        {
            get { return m_ImportNum; }
        }

        //xzr---add

        /// <summary>
        /// 获取或设置是否是用于数据处理
        /// </summary>
        public bool IsPurpose { get; set; }

        /// <summary>
        /// 是否修改实物管理部门
        /// </summary>
        public bool IsChangeDepartment
        {
            get { return m_IsChangeDepartment; }
            set { m_IsChangeDepartment = value; }
        }

        /// <summary>
        /// 是否对项目信息进行完整的验证
        /// </summary>
        public bool IsTotalCheckProject
        {
            get { return m_IsTotalCheckProject; }
            set { m_IsTotalCheckProject = value; }
        }

        #endregion

        #region 结果集操作

        /// <summary>
        /// 公开EXCEL结果集
        /// </summary>
        public DataTable SourceData
        {
            get { return m_SourceData; }
        }

        /// <summary>
        /// 获取正确或出错的源数据
        /// </summary>
        /// <param name="getCorrectData">是否获取正确或出错的源数据</param>
        /// <returns></returns>
        public DataTable GetSourceData(bool getCorrectData)
        {
            if (!Checked)
            {
                //throw new ImportException("在检查之前,获取检查不通过数据");
            }
            if (ErrorMessage.Rows.Count == 0)
            {
                if (getCorrectData)
                {
                    return m_SourceData;
                }
                else
                {
                    return new DataTable();
                }
            }

            DataTable result = m_SourceData.Clone();
            int size = m_SourceData.Rows.Count;
            for (int i = 0; i < size; i++)
            {
                int RowNumber = m_ExecelHelper.GetSourceDataIndex(i);
                if (ErrorMessage.InError(RowNumber) != getCorrectData)
                {
                    result.Rows.Add(m_SourceData.Rows[i].ItemArray);
                }
            }
            return result;
        }

        /// <summary>
        /// 获取错误的数据并附加错误说明
        /// </summary>
        /// <returns>DataTable</returns>
        public DataTable GetErrorSourceWithErrorMsg()
        {
            if (ErrorMessage.Rows.Count == 0)
            {
                return new DataTable();
            }

            DataTable result = m_SourceData.Clone();
            for (int k = 0; k < ErrorMessage.Columns.Count; k++)
            {
                result.Columns.Add(ErrorMessage.Columns[k].ColumnName, ErrorMessage.Columns[k].GetType());
            }

            int size = m_SourceData.Rows.Count;
            for (int i = 0; i < size; i++)
            {
                int RowNumber = m_ExecelHelper.GetSourceDataIndex(i);
                if (ErrorMessage.InError(RowNumber))
                {
                    var objs = new object[result.Columns.Count];
                    m_SourceData.Rows[i].ItemArray.CopyTo(objs, 0);
                    ErrorMessage.Rows[ErrorMessage.GetRowIndex(RowNumber)].ItemArray.CopyTo(objs,
                                                                                            m_SourceData.Columns.Count);
                    result.Rows.Add(objs);
                }
            }
            return result;
        }

        #endregion

        /// <summary>
        /// 检查列明是否和我们需要的一致
        /// </summary>
        /// <param name="sheetName">表名称</param>
        private void CheckTitle(string sheetName)
        {
            string message;
            if (!m_ExecelHelper.CheckTitle(sheetName, out message, Titles))
            {
                throw new ArgumentException(message);
            }
        }

        /// <summary>
        /// 开始检查数据
        /// </summary>
        /// <returns></returns>
        public bool Check()
        {
            return Check(m_SourceData);
        }

        /// <summary>
        /// 检查数据
        /// </summary>
        /// <param name="checkedData">数据集合</param>
        /// <returns></returns>
        private bool Check(DataTable checkedData)
        {
            if (m_ExecelHelper == null)
            {
                m_ExecelHelper = CreateExcelHelper(FilePath, SheetName);
                m_SourceData = m_ExecelHelper.GetDataTable();
                checkedData = m_SourceData;
            }

            m_ExecelHelper.CheckingEvent += CreateDBSearchCondition;

            /*
			 * 检查上传列数是否与预期一致,现在不检查了：因为可以使用缺少列，由程序自动补齐
			if( checkedData.Columns.Count!=Titles.Length )
			{
				throw new ImportException( string.Format("列的数量不匹配 预计列数{0},实际列数{1}",Titles.Length,checkedData.Columns.Count) );
			}*/

            //开始Excel标结构是否ok
            CheckTitle(SheetName);
            m_ErrorMessage = m_ExecelHelper.Check(checkedData);
            GetDBRecordSet();
            CheckWithDbRecord(m_ErrorMessage, checkedData);
            AfterCheckWithDBRecord(m_ErrorMessage, checkedData);
            m_Checked = true;
            if (m_ErrorMessage.Rows.Count == 0)
                return true;
              return false;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="errorResult"></param>
        /// <param name="checkedData"></param>
        protected virtual void AfterCheckWithDBRecord(ErrorResult errorResult, DataTable checkedData)
        {
        }

        /// <summary>
        /// 检查数据库
        /// </summary>
        /// <param name="errorResult"></param>
        /// <param name="dt"></param>
        private void CheckWithDbRecord(ErrorResult errorResult, DataTable dt)
        {
            int size = dt.Rows.Count;
            if (size > 0)
            {
                for (int i = 0; i < size; i++)
                {
                    DataRow dr = dt.Rows[i];
                    CheckInDb(dr, m_ExecelHelper.GetSourceDataIndex(i), errorResult);
                }
            }
            else
                errorResult.SetMessage(size, "EXCEL表中没有要验证的数据");
        }

        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="withCheck">是否检查</param>
        /// <param name="dt">数据</param>
        public void Import(DataTable dt, bool withCheck)
        {
            if (dt == null)
                throw new ArgumentNullException("dt");
            if (withCheck)
            {
                Check(dt);
            }
            else
            {
                Import(dt);
            }
        }

        private void Import(DataTable dt)
        {
            var ary = new object[dt.Rows.Count];
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                ary[i] = DataRowToObject(dt.Rows[i]);
            }
            var ar = new ArrayList(ary);
            if (ar.Count > 0)
                SaveObjects(ar);
        }

        /// <summary>
        /// 检查操作类型是否正确
        /// </summary>
        /// <param name="Operate">操作类型</param>
        /// <param name="Message">返回的消息</param>
        /// <returns>bool</returns>
        public bool CheckOperate(string Operate, out string Message)
        {
            Message = null;
            if (Operate.Length > 0 && !(Operate == "更新" || Operate == "导入" || Operate == "盘点导入"))
            {
                Message = "操作只能为\"导入\"或\"更新\",实际值为\"" + Operate + "\"";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 检查单元格的填写是否正确
        /// </summary>
        /// <param name="columnValue">值</param>
        /// <param name="columnHead">列头</param>
        /// <param name="Message">返回的消息</param>
        /// <returns>bool</returns>
        public bool CheckIsYesOrNo(string columnValue, string columnHead, out string Message)
        {
            Message = null;
            if (columnValue.Length > 0 && !(columnValue == "是" || columnValue == "否"))
            {
                Message = columnHead + "只能为\"是\"或\"否\",实际值为\"" + columnValue + "\"";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 检查操作类型是否正确
        /// </summary>
        /// <param name="address">操作类型</param>
        /// <param name="Message">返回的消息</param>
        /// <returns>bool</returns>
        public bool CheckAddress(string address, out string Message)
        {
            Message = null;
            if (address.Length > 200)
            {
                Message = "地址长度不能超过200";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 检查枚举值是否正确
        /// </summary>
        /// <param name="enumName">传入的枚举值名称</param>
        /// <param name="enumType">枚举类型</param>
        /// <returns>Y|N</returns>
        public bool CheckEnum(string enumName, Type enumType)
        {
            try
            {
                if (enumName.Length > 0)
                {
                    EnumTypeHelper.GetEnumValue(enumName, enumType);
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        public bool CheckEnumBySetting(DataRow row, string colName, string enumTypeName, out string msg)
        {
            msg = "";
            string val = ReturnColumnValue(row, colName);
            return CheckEnumBySetting(enumTypeName, val, out msg);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="enumTypeName"></param>
        /// <param name="enumValue"></param>
        /// <param name="msg"></param>
        /// <returns></returns>
        public bool CheckEnumBySetting(string enumTypeName, string enumValue, out string msg)
        {
            msg = "";
            NameValueCollection nvc = m_enumHelper.GetValue(enumTypeName);
            if (nvc.GetValues(enumValue)!=null)
            {
                msg = "对于\"" + enumTypeName + "\"类型,此值无效";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 检查日期格式是否正确
        /// </summary>
        /// <param name="row">行记录</param>
        /// <param name="columnName">列名称</param>
        /// <param name="Message">返回的消息</param>
        /// <returns></returns>
        public bool CheckDateTimeFarmat(DataRow row, string columnName, out string Message)
        {
            Message = null;
            string dateTime = Convert.ToString(row[columnName]).Trim();
            try
            {
                if (dateTime.Length > 0)
                {
                    DateTime d = Convert.ToDateTime(dateTime);
                    if (d.Year < 1900)
                        throw new ArgumentOutOfRangeException("日期", "日期必须在1900年以前");
                }
            }
            catch
            {
                Message = "\"" + columnName + "\"无效，请输入1900年到9999年之内有效的日期格式(YYYY-MM-DD)";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 检查相关的字段的长度是否符合要求
        /// </summary>
        /// <param name="field">字段值</param>
        /// <param name="fieldname">字段名称</param>
        /// <param name="minlength">最小长度</param>
        /// <param name="maxlength">最大长度</param>
        /// <param name="Message">返回的消息</param>
        /// <returns></returns>
        public static bool CheckFieldLength(string field, string fieldname, int minlength, int maxlength,
                                            out string Message)
        {
            Message = null;
            if (field.Length < minlength)
            {
                Message = "<font color=red>" + fieldname + "长度不能小于" + minlength + "（实际值长度为：" + field.Length + "）</font>";
                return false;
            }
            else
            {
                if (field.Length > maxlength)
                {
                    Message = "<font color=red>" + fieldname + "长度不能大于" + maxlength + "（实际值长度为：" + field.Length +
                              "）</font>";
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 检查是否有非法字符
        /// </summary>
        /// <param name="field">字段值</param>
        /// <param name="fieldname">字段名称</param>
        /// <param name="Message">返回的消息</param>
        /// <returns></returns>
        public bool CheckLawlessChar(string field, string fieldname, out string Message)
        {
            Message = null;
            if (field.IndexOf('%') != -1)
            {
                Message = "<font color=red>" + fieldname + "含有非法字符（%）</font>";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 检查百分比格式是否正确
        /// </summary>
        /// <param name="field"></param>
        /// <param name="fieldname"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public static bool CheckPercent(string field, string fieldname, out string Message)
        {
            Message = null;
            string strGroundTotal = Convert.ToString(field).Trim();
            if (strGroundTotal.Length > 0)
            {
                if (!Regex.IsMatch(strGroundTotal, @"^[-+]?[0-9]\d{0,2}(\.\d{1,2})?%$"))
                {
                    Message = fieldname + "百分比格式不正确";
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 检查相关的字段的长度是否符合要求
        /// </summary>
        /// <param name="row">行记录</param>
        /// <param name="fieldname">字段名称</param>
        /// <param name="minlength">最小长度</param>
        /// <param name="maxlength">最大长度</param>
        /// <param name="Message">返回的消息</param>
        /// <returns></returns>
        public static bool CheckFieldLength(DataRow row, string fieldname, int minlength, int maxlength,
                                            out string Message)
        {
            string field = Convert.ToString(row[fieldname]).Trim();
            return CheckFieldLength(field, fieldname, minlength, maxlength, out Message);
        }

        /// <summary>
        /// 验证整数
        /// </summary>
        /// <param name="field"></param>
        /// <param name="fieldname"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public bool CheckIsInt(string field, string fieldname, out string Message)
        {
            Message = null;
            string strGroundTotal = Convert.ToString(field).Trim();
            if (!Regex.IsMatch(strGroundTotal, @"^[+-]?\d*$"))
            {
                Message = fieldname + "必须为整数";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 验证正整数
        /// </summary>
        /// <param name="field"></param>
        /// <param name="fieldname"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public bool CheckIsPlusInt(string field, string fieldname, out string Message)
        {
            Message = null;
            string strGroundTotal = Convert.ToString(field).Trim();
            if (!Regex.IsMatch(strGroundTotal, @"^[+]?\d*$"))
            {
                Message = fieldname + "必须为非负整数";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 验证小数类型
        /// </summary>
        /// <param name="field"></param>
        /// <param name="fieldname"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public bool CheckIsDouble(string field, string fieldname, out string Message)
        {
            Message = null;
            string strGroundTotal = Convert.ToString(field).Trim();
            if (!Regex.IsMatch(strGroundTotal, @"^[+-]?\d*[.]?\d*$"))
            {
                Message = fieldname + "必须为有效的小数";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 验证小数类型
        /// </summary>
        /// <param name="field"></param>
        /// <param name="fieldname"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public bool CheckIsPlusDouble(string field, string fieldname, out string Message)
        {
            Message = null;
            string strGroundTotal = Convert.ToString(field).Trim();
            if (!Regex.IsMatch(strGroundTotal, @"^[+]?\d*[.]?\d*$"))
            {
                Message = fieldname + "必须为非负小数";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 验证日期格式
        /// </summary>
        /// <param name="yearMonthDate"></param>
        /// <param name="fieldHeader"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public static bool CheckYearMonthStringFormat(string yearMonthDate, string fieldHeader, out string Message)
        {
            Message = null;
            if (yearMonthDate.Length > 0)
            {
                if (!Regex.IsMatch(yearMonthDate, @"^(1[9]|[2-9]\d)\d{2}-(\d|1[0-2])$"))
                {
                    Message = fieldHeader + "格式不正确，请输入1900-1到9999-12之间的日期";
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 检查字段的长度是否为0
        /// </summary>
        /// <param name="strField"></param>
        /// <param name="fieldHeader"></param>
        /// <returns></returns>
        public static bool CheckFieldLengthNotZero(string strField, string fieldHeader)
        {
            if (strField.Length == 0)
            {
                return false;
            }
            return true;
        }

        /// <summary>
        /// 验证日期时间格式（yyyymmddhhmm）
        /// </summary>
        /// <param name="dateTime"></param>
        /// <param name="fieldHeader"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public bool CheckDateTimeAllFormat(string dateTime, string fieldHeader, out string Message)
        {
            Message = null;
            string regex =
                @"^((\d{2}(([02468][048])|([13579][26]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|([1-2][0-9])))))|(\d{2}(([02468][1235679])|([13579][01345789]))[\-\/\s]?((((0?[13578])|(1[02]))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(3[01])))|(((0?[469])|(11))[\-\/\s]?((0?[1-9])|([1-2][0-9])|(30)))|(0?2[\-\/\s]?((0?[1-9])|(1[0-9])|(2[0-8]))))))(\s(((0?[0-9])|(1[0-9])|(2[0-3]))\:(([0-5][0-9])|([0-9]))(((\s)|(\:(([0-5][0-9])|([0-9]))))?)))?$";

            if (dateTime.Length > 0)
            {
                if (!Regex.IsMatch(dateTime, regex))
                {
                    Message = fieldHeader + "格式不正确，请输入1900-1-1 00:00到9999-12-31 23:59之间的日期";
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 检查数据是否正确
        /// </summary>
        /// <param name="currentValue">传入的值</param>
        /// <param name="minValue">最小值</param>
        /// <param name="maxValue">最大值</param>
        /// <param name="columnHead">列头</param>
        /// <param name="Message">返回的消息</param>
        /// <returns>Y|N</returns>
        public static bool CheckValueSize(string currentValue, double minValue, double maxValue, string columnHead,
                                          out string Message)
        {
            try
            {
                Message = null;
                if (currentValue.Length > 0)
                {
                    double tempValue = Convert.ToDouble(currentValue);
                    if (tempValue < minValue)
                    {
                        Message = columnHead + ":不能小于" + minValue;
                        return false;
                    }
                    if (tempValue > maxValue)
                    {
                        Message = columnHead + ":不能大于" + maxValue;
                        return false;
                    }
                }
            }
            catch
            {
                Message = columnHead + "必须为有效的数值";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 判断日期格式
        /// </summary>
        /// <param name="yearMonthDate"></param>
        /// <param name="fieldHeader"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public static bool CheckYearMonthDayStringFormat(string yearMonthDate, string fieldHeader, out string Message)
        {
            Message = null;
            if (yearMonthDate.Length > 0)
            {
                if (!Regex.IsMatch(yearMonthDate, @"^(19|[2-9][0-9])\d{2}-(0?\d|1[012])-(0?\d|[12]\d|3[01])$"))
                {
                    Message = fieldHeader + "格式不正确，请输入1900-1-1到9999-12-31之间的日期";
                    return false;
                }
            }
            return true;
        }

        /// <summary>
        /// 判断日期格式，包括2-31[闰月]
        /// </summary>
        /// <param name="yearMonthDate"></param>
        /// <param name="fieldHeader"></param>
        /// <param name="Message"></param>
        /// <returns></returns>
        public static bool CheckYearMonthDayLeapStringFormat(string yearMonthDate, string fieldHeader,
                                                             out string Message)
        {
            Message = null;
            if (yearMonthDate.Length > 0)
            {
                if (
                    !Regex.IsMatch(yearMonthDate,
                                   @"((^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(10|12|0?[13578])([-\/\._])(3[01]|[12][0-9]|0?[1-9])$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(11|0?[469])([-\/\._])(30|[12][0-9]|0?[1-9])$)|(^((1[8-9]\d{2})|([2-9]\d{3}))([-\/\._])(0?2)([-\/\._])(2[0-8]|1[0-9]|0?[1-9])$)|(^([2468][048]00)([-\/\._])(0?2)([-\/\._])(29)$)|(^([3579][26]00)([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][0][48])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][0][48])([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][2468][048])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][2468][048])([-\/\._])(0?2)([-\/\._])(29)$)|(^([1][89][13579][26])([-\/\._])(0?2)([-\/\._])(29)$)|(^([2-9][0-9][13579][26])([-\/\._])(0?2)([-\/\._])(29)$))"))
                {
                    Message = fieldHeader + " [" + yearMonthDate + "] 格式不正确，请输入正确的日期格式";
                    return false;
                }
            }
            else if (yearMonthDate.Length == 0)
            {
                Message = fieldHeader + " [" + yearMonthDate + "] 格式不正确，请输入正确的日期格式";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 返回常规形式
        /// </summary>
        /// <param name="row"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public string ReturnColumnValue(DataRow row, string columnName)
        {
            string strTemp = Convert.ToString(row[columnName]).Trim();
            row[columnName] = strTemp;
            return strTemp;
        }

        /// <summary>
        /// 返回Upper形式
        /// </summary>
        /// <param name="row"></param>
        /// <param name="columnName"></param>
        /// <returns></returns>
        public string ReturnUpperColumnValue(DataRow row, string columnName)
        {
            string strTemp = Convert.ToString(row[columnName]).Trim().ToUpper();
            row[columnName] = strTemp;
            return strTemp;
        }

  
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sc"></param>
        /// <param name="colName"></param>
        /// <param name="row"></param>
        /// <param name="minLen"></param>
        /// <param name="maxlen"></param>
        /// <param name="message"></param>
        /// <param name="errorResult"></param>
        /// <param name="excelRowNumber"></param>
        protected void SetTheQueryCollection(StringCollection sc, string colName, DataRow row, int minLen, int maxlen,
                                             out string message, ErrorResult errorResult, int excelRowNumber)
        {
            string cellValue = ReturnColumnValue(row, colName);
            if (!CheckFieldLength(cellValue, colName, minLen, maxlen, out message))
                errorResult.SetMessage(excelRowNumber, message);
            else
            {
                if (sc != null && cellValue.Length != 0 && !sc.Contains(cellValue))
                    sc.Add(cellValue);
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nd"></param>
        /// <param name="row"></param>
        /// <param name="colName"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        protected bool CheckChildObjectExists(NameObjectDictionary nd, DataRow row, string colName, out string message)
        {
            string keyValue = ReturnColumnValue(row, colName);
            message = null;
            return CheckChildObjectExists(nd, keyValue, colName, out message);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="nd"></param>
        /// <param name="colValue"></param>
        /// <param name="colName"></param>
        /// <param name="message"></param>
        /// <returns></returns>
        protected bool CheckChildObjectExists(NameObjectDictionary nd, string colValue, string colName,
                                              out string message)
        {
            message = null;
            if (string.IsNullOrEmpty(colValue))
                return true;
            
            string keyValue = colValue;
            
            if (keyValue.Length != 0)
            {
                foreach (string str in nd.Keys)
                {
                    if (str.ToUpper() == keyValue.ToUpper())
                        return true;
                }
            }
            message = colName + "\"" + keyValue + "\"在数据库中不存在";
            return false;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="keyField"></param>
        /// <param name="keyValues"></param>
        /// <param name="accSession"></param>
        /// <returns></returns>
        public StringCollection GetExistsTableFieldValues(string tableName, string keyField, StringCollection keyValues,
                                                          ISession accSession)
        {
            var reValue = new StringCollection();
            var sqlHlp = new SqlHelper();
            if (keyValues.Count > 0)
            {
                bool isFirst = true;
                string headString = "select " + keyField + " from " + tableName + " c where ";
                var sb = new StringBuilder(headString);
                for (int i = 0; i < keyValues.Count; i++)
                {
                    if (!isFirst)
                        sb.Append(" or ");
                    else
                        isFirst = false;
                    sb.Append("c." + keyField + "='" + keyValues[i] + "'");

                    if ((i%100 == 0 && i != 0) || (i + 1 == keyValues.Count))
                    {
                        sb.Append("Group By " + keyField);
                        DataSet ds = sqlHlp.ExecuteDataSet(accSession, sb.ToString());

                        if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                        {
                            foreach (DataRow row in ds.Tables[0].Rows)
                            {
                                string keyValue = Convert.ToString(row[keyField]).ToUpper();
                                reValue.Add(keyValue);
                            }
                        }

                        sb = new StringBuilder(headString);
                        isFirst = true;
                    }
                }
            }
            return reValue;
        }

        protected void CheckInsertNoEmptyColumn(DataRow row, string[] titles, bool isInsert, ErrorResult errorResult,
                                                int excelRowNumber)
        {
            if (isInsert)
            {
                for (int i = 0; i < titles.Length; i++)
                {
                    if (Convert.ToString(row[titles[i]]).Trim().Length == 0)
                        errorResult.SetMessage(excelRowNumber, "在" + INSERT + "操作下,\"" + titles[i] + "\"不能为空");
                }
            }
        }

        protected void CheckUpdateNoDeleteField(DataRow row, string[] titles, bool isInsert, ErrorResult errorResult,
                                                int excelRowNumber)
        {
            if (!isInsert)
            {
                for (int i = 0; i < titles.Length; i++)
                {
                    string fieldValue = Convert.ToString(row[titles[i]]).Trim();
                    if (fieldValue.Length > 0 && fieldValue == DELETE_CONTENT)
                        errorResult.SetMessage(excelRowNumber,
                                               "在" + UPDATE + "操作下,\"" + titles[i] + "\"不能设置为\"" + DELETE_CONTENT +
                                               "\"（清除内容）");
                }
            }
        }

        protected void CheckInsertNoEmptyColumn(DataRow row, StringCollection titles, bool isInsert, ErrorResult errorResult,
                                               int excelRowNumber)
        {
            if (isInsert)
            {
                for (int i = 0; i < titles.Count; i++)
                {
                    if (Convert.ToString(row[titles[i]]).Trim().Length == 0)
                        errorResult.SetMessage(excelRowNumber, "在" + INSERT + "操作下,\"" + titles[i] + "\"不能为空");
                }
            }
        }

        protected void CheckUpdateNoDeleteField(DataRow row, StringCollection titles, bool isInsert, ErrorResult errorResult,
                                                int excelRowNumber)
        {
            if (!isInsert)
            {
                for (int i = 0; i < titles.Count; i++)
                {
                    string fieldValue = Convert.ToString(row[titles[i]]).Trim();
                    if (fieldValue.Length > 0 && fieldValue == DELETE_CONTENT)
                        errorResult.SetMessage(excelRowNumber,
                                               "在" + UPDATE + "操作下,\"" + titles[i] + "\"不能设置为\"" + DELETE_CONTENT +
                                               "\"（清除内容）");
                }
            }
        }


        protected bool IsNeedToUpdateInfo(bool isInsert, DataRow row, string colName)
        {
            string fieldValue = ReturnColumnValue(row, colName);
            return IsNeedToUpdateInfo(isInsert, fieldValue);
        }


        protected bool IsNeedToUpdateInfo(bool isInsert, string fieldValue)
        {
            if (fieldValue.Length > 0 && (isInsert || fieldValue != DELETE_CONTENT))
                return true;
            return false;
        }

        protected bool IsNeedToUpdateInfo(bool isInsert, DataRow row, string colName1, string colName2)
        {
            string fieldValue1 = ReturnColumnValue(row, colName1);
            string fieldValue2 = ReturnColumnValue(row, colName2);
            return IsNeedToUpdateInfo(isInsert, fieldValue1, fieldValue2);
        }

        protected bool IsNeedToUpdateInfo(bool isInsert, string fieldValue1, string fieldValue2)
        {
            if (fieldValue1.Length > 0 && fieldValue2.Length > 0 && (isInsert ||
                                                                     fieldValue1 != DELETE_CONTENT &&
                                                                     fieldValue2 != DELETE_CONTENT))
                return true;
            return false;
        }

     

        #region abstractMethod

        /// <summary>
        /// 获取检查标题顺序
        /// </summary>
        /// <remarks>
        /// 程序会根据返回的数据顺序，检查是否和Excel一致。这个方法会在使用<see cref="Check"/>,Check自动调用这个属性
        /// 然后再调用
        /// </remarks>
        /// <example>
        /// <code>
        /// /// <summary>
        /// 获取标题的顺序
        /// </summary>
        ///protected override string[] Titles
        ///{
        ///	get
        ///	{
        ///		return new string[]
        ///			{
        ///				"基站名称", "基站编码",
        ///				"所在机房名称", "基站类型", "网络类型",
        ///				"验收状态", "基站等级",
        ///				"管理部门", "覆盖范围描述",
        ///				"开通时间", "是否为新增数据", "备注"
        ///			};
        ///	}
        ///}
        /// </code>
        /// </example>
        protected abstract string[] Titles { get; }

        /// <summary>
        /// 从数据行转换成为需要导入的对象,用于检查完毕后进行对象保存的时候使用
        /// </summary>
        /// <param name="row">每次读的Dr信息</param>
        /// <returns>返回需要进行保存的对象</returns>
        protected abstract object DataRowToObject(DataRow row);

        /// <summary>
        /// 保存对象
        /// </summary>
        /// <param name="objects"></param>
        protected abstract void SaveObjects(IList objects);


        /// <summary>
        /// 获取数据库的数据,这个方法目的是为了获取数据库中数据集合，使他们可以和导入数据进行
        /// 对比。详细请查看哪<see cref="CreateDBSearchCondition"/>,他是整个过程的第一个。
        /// </summary>
        protected abstract void GetDBRecordSet();

        /// <summary>
        /// 获取<see cref="ExcelHelper"/>对象,这个对象用于检查Excel的表结构
        /// </summary>
        /// <param name="path">Excel文件的路径</param>
        /// <param name="sheetName">Sheet的名称</param>
        /// <example>
        /// <code>
        ///	protected override ExcelHelper CreateExcelHelper(string path, string sheetName)
        ///	{
        ///		ExcelHelper helper = new ExcelHelper(path, sheetName);
        /// //设定不能重复的列名称
        ///	helper.NotRepleateColumns.AddColumnByName("基站名称");
        ///	helper.NotRepleateColumns.AddColumnByName(E_CODE);
        /// //设备定不能为空的列名称
        ///	helper.NotEmptyColumns.AddColumnByName("所在机房名称");
        ///	helper.NotEmptyColumns.AddColumnByName("基站类型");
        ///	helper.NotEmptyColumns.AddColumnByName("网络类型");
        ///	helper.NotEmptyColumns.AddColumnByName("管理部门");
        ///	helper.NotEmptyColumns.AddColumnByName("覆盖范围描述");
        ///	helper.NotEmptyColumns.AddColumnByName("基站等级");
        ///
        ///		return helper;
        ///	}
        /// </code>
        /// </example>
        /// <returns></returns>
        protected abstract ExcelHelper CreateExcelHelper(string path, string sheetName);

        /// <summary>
        /// 从Excel表格中的行，获取数据，产生查询条件,在第一次Excel表格结构检查后（除了标题检查失败会直接终止），会
        /// 调用<see cref="GetDBRecordSet"/>，在这个方法编写数据访问代码，获取数据。然后再调用<see cref="CheckInDb"/>
        /// 方法进行与数据集对比。
        /// </summary>
        /// <param name="row">EXCEL行数据</param>
        /// <param name="errorResult">错误信息</param>
        /// <param name="excelRowNumber">错误行号</param>
        protected abstract void CreateDBSearchCondition(DataRow row, ErrorResult errorResult, int excelRowNumber);

        /// <summary>
        /// 与数据库中的数据进行对比
        /// </summary>
        /// <param name="row">Excel读取的行对象</param>
        /// <param name="ExcelRowNumber">Excel表真实对应的行数</param>
        /// <param name="errMessage">错误信息的对象</param>
        protected abstract void CheckInDb(DataRow row, int ExcelRowNumber, ErrorResult errMessage);

        #endregion
    }
}
